//get grant schedule over income and ability from npsas tables I downloaded.

//merit grants_avg_10k_upto2000_cleaned//merit adjustments
import delimited "$data/npsas/merit_by_gpa_cleaned.csv", clear
gen cat = 1
replace cat = 2 if v3 == "p4y"
replace cat = 3 if v3 == "bpr"
replace cat = 4 if v3 == "gpr"
drop v3
drop if _n>20

gen gpa = word(v1, -1)
drop if gpa == "Total"
gen ability = 0
replace ability = .333 if gpa == "200"
replace ability = .666 if gpa == "300"
replace ability = 1.0 if gpa == "301"
drop gpa

replace v2 = subinstr(v2, "!", "", .)
replace v2 = trim(v2)
destring v2, replace

ren v2 grant_merit
drop v1
replace grant_merit = grant_merit/.78235 //deflate
replace grant_merit = grant_merit/10000 //normalize, x4


//estimate linear relationship between ability and grant aid for each category
forval i = 1/4{
	reg grant_merit ability if cat == `i'
	local int`i' = _b[_cons]
	local slope`i' = _b[ability]
}

clear
set obs 4
gen cat = _n
gen intercept = .
gen slope = .

forval i = 1/4{
	replace intercept = `int`i'' in `i'
	replace slope = `slope`i'' in `i'
}

ren intercept int_ability
ren slope slope_ability
export delimited "$model/utilities/grants_merit.csv", novarn replace
save "$temp/npsas_merit", replace


//get grants
import delimited "$data/npsas/grants_avg_10k_upto2000_cleaned.csv", clear
gen cat = 1
replace cat = 2 if v4 == "p4y"
replace cat = 3 if v4 == "bpr"
replace cat = 4 if v4 == "gpr"
drop v4

//parent income bounds
gen pinc_upper = word(v1, -1)
drop if pinc_upper == "Total"
destring pinc_upper, replace
drop v1
drop if _n>56

//federal aid
ren v3 aid_fed
destring aid_fed, replace force
replace aid_fed = 0 if aid_fed == .

//state aid
replace v2 = "450" in 39
replace v2 = "450" in 40
replace v2 = "450" in 41
replace v2 = "450" in 42
replace v2 = "85" in 8
replace v2 = "85" in 9
replace v2 = "85" in 10
replace v2 = "85" in 11
replace v2 = "85" in 12
replace v2 = "85" in 13
replace v2 = "85" in 14
replace v2 = subinstr(v2, "!", "", .)
replace v2 = trim(v2)
destring v2, replace
destring pinc_upper, replace
ren v2 aid_state

gen pinc_g5 = (pinc>50000) //over 50,000 dollars in 2000

//deflate and normalize
ds cat pinc_g5, not
foreach var in `r(varlist)'{
	replace `var' = `var'/ .78235
	replace `var' = `var' / 10000 //now measured in $10,000 2012 dollars
}
ren pinc_upper pinc

//create some spline functions to approximate financila aid policies
forval i = 1/4{
	reg aid_fed pinc pinc_g5 c.pinc#i.pinc_g5 if cat == `i'
	local cons_fed_`i' = _b[_cons]
	local pinc_fed_`i' = _b[pinc]
	local g5_fed_`i' = _b[pinc_g5]
	local int_fed_`i' = _b[1.pinc_g5#c.pinc]
	predict aid_fed_predict_`i' if cat == `i'
	
	reg aid_state pinc pinc_g5 c.pinc#i.pinc_g5 if cat == `i'
	local cons_state_`i' = _b[_cons]
	local pinc_state_`i' = _b[pinc]
	local g5_state_`i' = _b[pinc_g5]
	local int_state_`i' = _b[1.pinc_g5#c.pinc]
	predict aid_state_predict_`i' if cat == `i'
}



forval i = 1/4{
twoway connected aid_fed pinc if cat == `i' || ///
connected aid_fed_predict_`i' pinc if cat == `i', lp(dash) color(stc1) || ///
connected aid_state pinc if cat == `i', color(stc2) || ///
connected aid_state_predict_`i' pinc if cat == `i', color(stc2) lp(dash) xtitle("Parent Income") ytitle("Annual Financial Aid") ///
legend(lab(1 "Federal Aid (Reported)") lab(2 "Federal Aid (Predicted)") lab(3 "State Aid (Reported)") lab(4 "State Aid (Predicted)"))
graph export "$output/aid_schedule_`i'.png", replace
graph close
}



//now store coefficients to use in model
clear
set obs 8
gen cat = .
gen cons = .
gen pinc = .
gen g5 = .
gen inter = .

forval i = 1/4{
	replace cat = `i' in `i'
	replace cons = `cons_fed_`i'' in `i'
	replace pinc = `pinc_fed_`i'' in `i'
	replace g5 = `g5_fed_`i'' in `i'
	replace inter = `int_fed_`i'' in `i'
}


forval i = 1/4{
	local row = `i' + 4
	replace cat = `i' in `row'
	replace cons = `cons_state_`i'' in `row'
	replace pinc = `pinc_state_`i'' in `row'
	replace g5 = `g5_state_`i'' in `row'
	replace inter = `int_state_`i'' in `row'
}


export delimited "$model/utilities/aid_schedule_spline.csv", replace novarn




 










